Optimal Excel structure for importing to Rational Doors

I'm having some serious problems importing spreadsheets to DOORS. I have a sample spreadsheet with some headings, text , requirements etc.  (see attachment test10.xlsx). 

- Firstly, I use Excel to convert to .csv format. Excel uses ; to separate different cells, DOORS doesn't seem to like this so first I have to convert all the ; to commas (,). 

- Then I import the .csv into DOORS, in the Import Spreadsheet pop-up window, I use Import to attributes: From List, Import options: Create new objects. From the "Attributes to import:" field i just add one "Object Identifier" for the ID column in my spreadsheet, and 8 "Object Heading" for the remaining 8 cells. In the future I'll probably add some more attribute types like "requirement_type", "priority" etc., but for now, "object heading" will do. DOORS creates objects from the spreadsheet on the same level either way right? I'll probably have to fix the object hierarchy myself?

This, however, does not work at all, I have attached the result as a .png in the attachments. Does anybody know how I can import .csv files in a proper (and/or easier) way? And yes, I have looked at the various documentation that IBM provides for DOORS, could not find a solution. 

Sorry for long post, a lot to read, but thanks anyway :)

Re: Optimal Excel structure for importing to Rational Doors
HazelWoodcock - Tue Jun 18 08:32:31 EDT 2013

I will list my comments...

1. Each column of your spreadsheet should contain one attribute.
2. If you are not updating existing objects then the Object Identifier column does not help you much
3. DOORS can import csv or tsv, so that is separated with commas or tabs.
4. DOORS cannot import structure (hierarchy) from a spreadsheet, so adding an attribute for level will help you sort this out later.
5. If you put the attribute names in the first row of the spreadsheet, DOORS can use these to create any necessary new attributes, I don't think these will automatically be displayed for you in your view.
6. Where you have 'Some descriptive text if necessary' this appears to be an informational object that should reside below the heading - it is not generally considered good practice to mix object heading and text in a single object.
7. I have attached a .csv file that I think does what you are after, this was created by using 'Save as' from Excel and selecting CSV (MS-DOS) (*.CSV)

Re: Optimal Excel structure for importing to Rational Doors
kip820 - Tue Jun 18 08:47:43 EDT 2013

Aweseome, thanks for a quick and informative reply. Will try it out immediately, just a small follow up question: Why did you use CVS (MS-DOS)(*.CVS) instead of CVS(Comma delimited)(*.CVS)? This might just be a version dependent thing, I'm using Microsoft Office Professional Plus 2010, Excel version 14, and those two are listed as seperate "save as" options, was just wondering why you used the one instead of the other.

Re: Optimal Excel structure for importing to Rational Doors
HazelWoodcock - Tue Jun 18 08:58:19 EDT 2013

I carefully chose the format I did because it was the first CSV one I spotted in the list and when I checked with a text editor, it had produced the right result :-)

If you are trying to import a large document with detailed structure then you might have to get a bit clever with it.  You can import structure from Word, and attributes from Excel, so if you can get the structure first you should then be able to update existing objects with a spreadsheet import, but you have to make sure you have a unique attribute to key off.  It can get quite tricky.  Also check out the DXL forum for hints on using a script to add the structure when you have created a flat import.  Restructuring a large module can be frustrating and should be done with frequent saves.

Re: Optimal Excel structure for importing to Rational Doors
kip820 - Tue Jun 18 09:06:07 EDT 2013

Thanks a bunch, what would I've done without you ;) The document I'm supposed to import is pretty large, so DXL definetely sounds promising for the task of restructuring the model after a flat import

Re: Optimal Excel structure for importing to Rational Doors
PRM - Tue Jun 18 18:01:44 EDT 2013

Additional to Hazel's response above, there are some steps that you can follow to rebuild the hierarchy using a DXL script created by Michael Sutherland. I have attached some screen grabs which relate to the steps below. This can be tedious but I think its much quicker than trying to manually promote & demote objects in DOORS to replicate a hierarchy.

Step 1 - In your speadsheet, create a new column called Hierarchy - in the DOORS module that you will import into, create a string attribute called Hierarchy. Use this column in the spreadsheet to enter the heading numbers for each row that contains a heading. Import the spreadsheet but I would recommend mapping your column that has the headings and text into the "Object Text" attribute. See attached screen grab "Step-01.png" - this shows the results after import and also the heading numbers used in the Hierarchy column - the Module Explorer on left hand side reflects that it is a flat hierarchy.

Step 2 - Run the attached DXL script "Galactic_Build Hierarchy.dxl" - choose the attribute which contains the hierarchy information

Step 3 - The Module Explorer on the left hand side now shows that the objects have been pushed into the hierarchy as defined by the Hierarchy attribute - BTW you may need to refresh the View first to refresh and force the Module Explorer to show the hierarchy.

Step 4 - On each row that contains a heading, use the "Swap Object Heading and Text" icon to push the heading text into the "Object Heading" attribute and this convert that row into a heading object. The screen grab shows my mouse cursor hovering over this icon.

If you want to save having to do Step 4 - then in your spreadsheet, have a column for rows that will be headings only and a column for rows that will be text only and then map these to the "Object Heading" and "Object Text" attributes on import.